package com.simple.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.simple.entity.SysMenu;
import com.simple.entity.vo.SysMenuVo;

import java.util.List;

public interface SysMenuRepository extends JpaRepository<SysMenu,Integer> {
    @Query(value="select * from sys_menu  where id=?1 " ,nativeQuery=true)
    public SysMenu findByMenuId(Integer id);
    //根据栏目ID，查询结果带父类栏目名称
    @Query(value="SELECT b.menu_name AS parentMenuName,"+ 
    		"a.id AS id,"+
    		"a.menu_name AS menuName,"+
    		"a.parent_id AS parentId,"+
    		"a.menu_address AS menuAddress,"+
    		"a.permission AS permission,"+
    		"a.menu_status AS menuStatus,"+
    		"a.is_blank AS isBlank,"+
    		"a.sort AS sort ,"+
    		"a.menu_level AS level "+
    		" FROM sys_menu a LEFT JOIN  sys_menu b ON   b.id=a.parent_id  WHERE a.id=?1" ,nativeQuery=true)
    public List<SysMenuVo> findByMenuIdVo(Integer id);

    @Query(value="select * from sys_menu  where id in (?1)   order by id desc " ,nativeQuery=true)
    public List<SysMenu> findByMenuIds(String [] ids);

    @Query(value="select * from sys_menu  where id in (?1)  and menu_status=0 " ,nativeQuery=true)
    public List<SysMenu> findByMenuIdsStatus(String [] ids);

    @Query(value="select * from sys_menu a  where a.id in (?1)  group by a.parent_id ORDER BY a.sort" ,nativeQuery=true)
    public List<SysMenu> findByMenuIdsGroup(String [] ids);
    
    //查询等级，
    @Query(value="select * from sys_menu  where menu_level=?1 and del_status=0 order by sort" ,nativeQuery=true)
    public List<SysMenu> findByLevel(Integer level);

    @Query(value="select * from sys_menu  where id not in (0) AND del_status=0" ,nativeQuery=true)
    public List<SysMenu> findAll();
    
    @Modifying
    @Query(value="update  sys_menu set del_status=1   WHERE  id=?1" ,nativeQuery=true)
    public void deleteById(Integer id);

    //根据父Id查询子
    @Query(value="select * from sys_menu  where parent_id=?1 AND del_status=0 AND menu_status=0 order by sort" ,nativeQuery=true)
    public List<SysMenu> findByParentId(Integer id);

    //截取二级栏目
    @Query(value="select id  from sys_menu  where id  in (?1) AND menu_level=2 AND del_status=0" ,nativeQuery=true)
    public List<Object> findSecondLevel(int[] ids);

    //根据已知的栏目查所在的二级栏目
    @Query(value="select * from  (select * from sys_menu  where id in (?1)  and menu_status=0) a  where a.parent_id=?2" ,nativeQuery=true)
    public List<SysMenu> findByInParentId(String [] ids,Integer parentId);
    
}
